package pl.asoft.nbp.dao.impl;

import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import pl.asoft.nbp.dao.ExchangeRateDAO;
import pl.asoft.nbp.model.ExchangeRate;

import javax.persistence.MappedSuperclass;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;

import java.util.Date;
import java.util.List;

import static pl.asoft.nbp.dao.QueryParameterBuilder.parameter;


/**
 * Created by rbiedrawa on 2014-12-02.
 */

@Repository("exchangeRateDAO")
@Transactional(readOnly = true)

@MappedSuperclass
@NamedQueries({
        @NamedQuery(name = "ExchangeRate.findByShortName", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.currency.shortName = :shortName"),
        @NamedQuery(name = "ExchangeRate.findByShortNameAndDate", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.currency.shortName = :shortName AND e.dateOfPublication = :dateOfPublication"),
        @NamedQuery(name = "ExchangeRate.findByDate", query = "FROM ExchangeRate  e JOIN FETCH e.currency WHERE e.dateOfPublication = :dateOfPublication"),

        //FIXME: Simplify named queries (DRY/DIE). Create CRITERIA QUERY IN GENERIC DAO

        @NamedQuery(name = "ExchangeRate.withMaxPrice", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.currency.shortName = :shortName and e.price = (SELECT MAX(price) FROM ExchangeRate er where er.currency.shortName = :shortName)"),
        @NamedQuery(name = "ExchangeRate.withMaxPriceAndDateBetween", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.currency.shortName = :shortName and e.dateOfPublication between :startDate and :endDate and e.price  = (SELECT MAX(er.price) FROM ExchangeRate er where er.currency.shortName = :shortName and er.dateOfPublication between :startDate and :endDate) "),
        @NamedQuery(name = "ExchangeRate.withMaxPriceAndDateBetweenWithoutShortName", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.dateOfPublication between :startDate and :endDate and e.price  = (SELECT MAX(er.price) FROM ExchangeRate er where er.dateOfPublication between :startDate and :endDate) "),

        @NamedQuery(name = "ExchangeRate.findByShortNameOrderByPriceDESC", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.currency.shortName = :shortName ORDER BY e.price DESC"),
        @NamedQuery(name = "ExchangeRate.findByShortNameOrderByPriceASC", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.currency.shortName = :shortName ORDER BY e.price ASC"),

        @NamedQuery(name = "ExchangeRate.withMinPrice", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.currency.shortName = :shortName and e.price = (SELECT MIN(price) FROM ExchangeRate er where er.currency.shortName = :shortName)"),
        @NamedQuery(name = "ExchangeRate.withMinPriceAndDateBetween", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.currency.shortName = :shortName and e.dateOfPublication between :startDate and :endDate and e.price  = (SELECT MIN(er.price) FROM ExchangeRate er where er.currency.shortName = :shortName and er.dateOfPublication between :startDate and :endDate) "),

        @NamedQuery(name = "ExchangeRate.Average", query = "SELECT AVG(e.price) FROM ExchangeRate e WHERE e.currency.shortName = :shortName"),
        @NamedQuery(name = "ExchangeRate.AverageFromDates", query = "SELECT AVG(e.price) FROM ExchangeRate e WHERE e.currency.shortName = :shortName and e.dateOfPublication between :startDate and :endDate"),
        @NamedQuery(name = "ExchangeRate.aboveAverage", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.currency.shortName = :shortName  and e.price > (SELECT AVG(er.price) FROM ExchangeRate er WHERE er.currency.shortName = :shortName  )"),
        @NamedQuery(name = "ExchangeRate.belowAverage", query = "FROM ExchangeRate e JOIN FETCH e.currency WHERE e.currency.shortName = :shortName and e.price < (SELECT AVG(er.price) FROM ExchangeRate er WHERE er.currency.shortName = :shortName )"),

})
public class ExchangeRateDAOImpl extends PersistentObjectDAOImpl<ExchangeRate, Long> implements ExchangeRateDAO {


    @Override
    public ExchangeRate findExchangeRateWithMaxPrice(Date startDate, Date endDate) {
    
	return	(ExchangeRate) sf.getCurrentSession().getNamedQuery(MAX_PRICE_AND_DATE_BETWEEN)
    											 .setParameter("startDate", startDate)
    											 .setParameter("endDate", endDate).uniqueResult();
    }
	
    @Override
    public List<ExchangeRate> findAllByDateBetween(final String currency,final Date startDate,final Date endDate) {

        Criteria criteria = sf.getCurrentSession().createCriteria(ExchangeRate.class);
        criteria.createAlias("currency", "currency");
        criteria.setFetchMode("currency", FetchMode.SELECT);
        if (startDate != null && endDate != null) {
            criteria.add(Restrictions.between("dateOfPublication", startDate, endDate));
        }
        if (!currency.equalsIgnoreCase("ALL")) {
            criteria.add(Restrictions.eq("currency.shortName", currency));
        }

        return criteria.list();
    }

    @Override
    public ExchangeRate findByShortNameAndDate(final String shortName,final Date date) {
        List list = findByNamedQuery(FIND_BY_SHORT_NAME_AND_DATE,
                                     parameter("shortName", shortName)
                                     .and("dateOfPublication", date)
                                     .build());
        if (list.isEmpty()) {
            return null;
        }
        return (ExchangeRate) list.get(0);

    }

    @Override
    public List<ExchangeRate> findByShortName(final String shortName) {
        return (List<ExchangeRate>) findByNamedQuery(FIND_BY_SHORT_NAME,
                parameter("shortName", shortName).build());
    }

    @Override
    public List<ExchangeRate> findByDate(final Date date) {
        return (List<ExchangeRate>) findByNamedQuery(FIND_BY_DATE, parameter("dateOfPublication", date).build());
    }

    @SuppressWarnings("unchecked")
	@Override
    public List<ExchangeRate> findAllWithMaxPriceLimit(final String shortName,final int limitResult) {
        return (List<ExchangeRate>) findByNamedQuery(FIND_BY_SHORT_NAME_ORDER_BY_PRICE_DESC, parameter("shortName", shortName).build(), limitResult);
    }

    @Override
    public List<ExchangeRate> findAllWithMinPriceLimit(final String shortName,final int limitResult) {
        return findByNamedQuery(FIND_BY_SHORT_NAME_ORDER_BY_PRICE_ASC,parameter("shortName",shortName).build(), limitResult);
    }

    @Override
    public List<ExchangeRate> findExchangeRateWithMaxPrice(final String shortName) {
        return findByNamedQuery(FIND_WITH_MAX_PRICE, parameter("shortName", shortName).build());
    }

    @Override
    public List<ExchangeRate> findExchangeRateWithMaxPrice(final String shortName,final Date startDate,final Date endDate) {

        return findByNamedQuery(FIND_WITH_MAX_PRICE_AND_DATE_BETWEEN,
                                                                    parameter("shortName",shortName)
                                                                    .and("startDate", startDate)
                                                                    .and("endDate", endDate).build());
    }


    @Override
    public List<ExchangeRate> findExchangeRateWithMinPrice(final String shortName) {
        return findByNamedQuery(FIND_WITH_MIN_PRICE, parameter("shortName", shortName).build());
    }

    @Override
    public List<ExchangeRate> findExchangeRateWithMinPrice(final String shortName,final Date startDate,final Date endDate) {
        return findByNamedQuery(FIND_WITH_MIN_PRICE_AND_DATE_BETWEEN,
                                parameter("shortName",shortName).and("startDate",startDate).and("endDate",endDate).build());
    }

    @Override
    public Double findExchangeRateAverage(final String shortName) {
        List avgList = findByNamedQuery(GET_AVERAGE, parameter("shortName", shortName).build());

        if (avgList.isEmpty()) {
            return null;
        }
        return (Double) avgList.get(0);

    }

    @Override
    public Double findExchangeRateAverage(String shortName, Date startDate, Date endDate) {

        List avgList = findByNamedQuery(GET_AVERAGE_FROM_DATES, parameter("shortName", shortName)
                                                                  .and("startDate", startDate)
                                                                  .and("endDate", endDate)
                                                                  .build());
        if (avgList.isEmpty()) {
            return null;
        }
        return (Double) avgList.get(0);
    }

    @Override
    public List<ExchangeRate> findAllBelowAverage(final String shortName) {
        return findByNamedQuery(BELOW_AVERAGE, parameter("shortName", shortName).build());
    }

    @Override
    public List<ExchangeRate> findAllAboveAverage(final String shortName) {
        return findByNamedQuery(ABOVE_AVERAGE, parameter("shortName", shortName).build());
    }

    @Override
    public List<ExchangeRate> findAllExistingEagerLoad() {
        Criteria criteria = sf.getCurrentSession().createCriteria(ExchangeRate.class);
        criteria.createAlias("currency", "currency");
        criteria.setFetchMode("currency", FetchMode.SELECT);

        return criteria.list();
    }

}
